﻿USE [DCSDownloadCenterDB]
GO
/****** Object:  StoredProcedure [dbo].[up_GetSyncMessagesByMessageTypeNew]    Script Date: 05/03/2011 17:56:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Yunjiang Zhang>
-- Create date: <2011-04-25>
-- Description: <[up_GetDCSFilesGroupByMessageType]>
-- Reviewed:	Lance Zhang
-- Review date: 2011-4-26
-- Modify:		Lance Zhang
-- Review date: 2011-4-28
-- Modify:		Lance Zhang
-- Review date: 2011-5-03

-- =============================================
ALTER PROCEDURE [dbo].[up_GetSyncMessagesByMessageTypeNew]
(
	@MessageType			VARCHAR(16)	= NULL,
	@GroupCount				INT			= 0
)
AS
BEGIN
    /************************************************* SET CONFIG *************************************************/
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	IF @GroupCount = 0
		SET @GroupCount = 100
    
    /********************************************** DECLARE VARIABLE **********************************************/
	DECLARE			@Rubbish		TABLE (FileId INT)
    
    /*********************************************** BEGIN LOGICAL ************************************************/

	BEGIN
		IF @MessageType = NULL
			RETURN
		
		BEGIN
		
			-- 0. Import all recent files.
			SELECT
				  [FileId]
				, [RemoteFileName]
				, SUBSTRING([RemoteFileName], 1 ,19) AS GroupName
				, [Status]
				, [DownloadTime]
			INTO
				#TEMP_RECENT_FILE
			FROM
				[DCSFile] WITH(NOLOCK)
			WHERE
				[MessageType] = @MessageType
			AND
				[DownloadTime] > GETDATE() - 0.016
			AND
				[Status] & 256 = 0 AND [Status] & 512 = 0	
				
				
			-- 1. Mark Rubbish to 512 directly				
			UPDATE
				[DCSFile] WITH(ROWLOCK)
			SET
				[Status] = [Status] | 512,
				[ESBSyncTime] = GETDATE()
			OUTPUT inserted.FileId INTO @Rubbish
			WHERE
				[FileId] IN
				(
					SELECT 
						FileId
					FROM
						#TEMP_RECENT_FILE
					WHERE
						GroupName
						IN
						(
							SELECT 
								SUBSTRING([RemoteFileName], 1 ,19)
							FROM
								[DCSFile] WITH(NOLOCK)
							WHERE
								[MessageType] = @MessageType
							AND
								[DownloadTime] > GETDATE() - 1
							AND
								[Status] & 256 = 256 OR [Status] & 512 = 512	
						)
				)
				
			-- 2. Remove Rubbish from temp.
			DELETE FROM
				#TEMP_RECENT_FILE
			WHERE
				[FileId]
				IN
				(
					SELECT 
						FileId
					FROM
						@Rubbish
				)
			
			-- 3. Remove UnParse files.
			DELETE FROM
				#TEMP_RECENT_FILE
			WHERE
				[Status] & 16 =0
			AND
				[DownloadTime] > GETDATE() - 0.008
			
			-- 3. ALL NEED TO SEND MESSAGE.
			SELECT 
				  [FileId]
				, [RemoteFileName]
				, [Status]
			FROM
				#TEMP_RECENT_FILE
				
			
			-- 4. ALL PRLS NEED TO SEND.
			SELECT
				P.*
			FROM
				[PRLMessage] P WITH(NOLOCK), #TEMP_RECENT_FILE T
			WHERE
				P.[RefFileId] = T.[FileId]
				
			-- 5. drop temp table.
			DROP TABLE #TEMP_RECENT_FILE
			
		END
	END
END


